package com.navi.loaddata.loadService.impl.edaDifference;

import com.navi.loaddata.core.etlException.ETLException;
import com.navi.loaddata.core.mysqlEntity.EdaDiffD;
import com.navi.loaddata.core.mysqlEntity.EdaDiffDModel;
import com.navi.loaddata.core.mysqlEntity.EtlConfDModel;
import com.navi.loaddata.core.repository.impl.MesRepositoryImpl;
import com.navi.loaddata.core.util.JdbcUtil;
import com.navi.loaddata.core.util.LoggerUtil;
import com.navi.loaddata.core.util.queryRunnerTest.QueryRunnerTest;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.util.HashMap;
import java.util.List;

/**
 * @author xufan
 */
@Service
//@Transactional(transactionManager = "primaryPlatformTransactionManager")
public class LoadEdaHisDiffService {
    private static final Logger logger = LoggerFactory.getLogger(LoadEdaHisDiffService.class);
    private LoggerUtil loggerUtil = new LoggerUtil(this.getClass());
    @Autowired
    private MesRepositoryImpl mesDao;
    @Autowired
    private SaveEdaDiff saveEdaDiff;

    public String[] loadEdaDiffMain(EtlConfDModel etlConfD) throws Exception {
        String[] resMsg = new String[3];

//        Session session = dao3.getEntityManagerSession();
        try {
            resMsg = loadEdaDiff(etlConfD);
            String msg = etlConfD.getOpe();

            if (StringUtils.isEmpty(etlConfD.getOpe())) msg = "ALL";
            logger.info("OpeId={} loadEdaHisDiffMain finish!", msg);
        } catch (ETLException e) {
            throw new ETLException(e.getMessage());
        }
        return resMsg;
    }

    /**
     * Load Measure Data
     * <p>
     * load base -> session table
     * load defect data  -> session table
     * call base proc  -> auto commit
     * call defect proc -> no auto commit
     * delete defect session table
     *
     * @param etlConfD
     */
    private String[] loadEdaDiff(EtlConfDModel etlConfD) throws Exception {
        String[] resMsg = new String[3];
        int okCnt = 0;
        int errCnt = 0;
        String errMsg = null;
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
        Timestamp startTime = etlConfD.getRunStartTimestamp();
        Timestamp endTime = etlConfD.getRunEndTimestamp();
        String start = sdf.format(startTime.getTime()).substring(0, 14);
        String end = sdf.format(endTime.getTime()).substring(0, 14);
        Timestamp updateTime = Timestamp.valueOf(LocalDateTime.now());

        QueryRunner qr_mes = JdbcUtil.getQueryRunner_mes();
        QueryRunner qr_ora = JdbcUtil.getQueryRunner_ora_edaldr();
        QueryRunnerTest test = new QueryRunnerTest();
        // mes  cnt
        String arrMesSql = "SELECT /*+INDEX(SEG MES_PRODUCTHISTORY_IDX06)*/ SUBFACTORYNAME as shop," +
                " OLDPROCESSOPERATIONNAME as ope," +
                " PRODUCTNAME as glassId," +
                " EVENTTIME as evtTimstamp," +
                " TIMEKEY as timekey" +
                " FROM MES_PRODUCTHISTORY SEG WHERE  1=1" +
                " AND TIMEKEY>='" + start + "'" +
                " AND TIMEKEY<'" + end + "'" +
                " AND SUBFACTORYNAME ='ARRAY'" +
                " AND EVENTNAME='TrackOut'  " +
                " AND LINKKEY IS NOT NULL" +
                " AND INSTR(OLDPROCESSOPERATIONNAME,'DUM')=0" +
                " AND CASE WHEN LENGTH(OLDPROCESSOPERATIONNAME)>=6 THEN SUBSTR(OLDPROCESSOPERATIONNAME,6,6) ELSE 'X' END <>'D'" +
                " AND OLDPROCESSOPERATIONNAME NOT IN ('A1100','C1100')" +
                " and LENGTH(PRODUCTNAME)=10";

        String cfMesSql = "SELECT /*+INDEX(SEG MES_PRODUCTHISTORY_IDX06)*/ SUBFACTORYNAME as shop," +
                " OLDPROCESSOPERATIONNAME as ope," +
                " PRODUCTNAME as glassId," +
                " EVENTTIME as evtTimstamp," +
                " TIMEKEY as timekey" +
                " FROM MES_PRODUCTHISTORY SEG WHERE  1=1" +
                " AND TIMEKEY>='" + start + "'" +
                " AND TIMEKEY<'" + end + "'" +
                " AND SUBFACTORYNAME ='CF'" +
                " AND EVENTNAME='TrackOut'  " +
                " AND LINKKEY IS NOT NULL" +
                " AND INSTR(OLDPROCESSOPERATIONNAME,'DUM')=0" +
                " AND CASE WHEN LENGTH(OLDPROCESSOPERATIONNAME)>=6 THEN SUBSTR(OLDPROCESSOPERATIONNAME,6,6) ELSE 'X' END <>'D'" +
                " AND OLDPROCESSOPERATIONNAME NOT IN ('A1100','C1100')" +
                " and LENGTH(PRODUCTNAME)=10";

        String cell1MesSql = "SELECT /*+INDEX(SEG MES_PRODUCTHISTORY_IDX06)*/ 'CELL1' as shop," +
                " OLDPROCESSOPERATIONNAME as ope," +
                " PRODUCTNAME as glassId," +
                " EVENTTIME as evtTimstamp," +
                " TIMEKEY as timekey" +
                " FROM MES_PRODUCTHISTORY SEG WHERE  1=1" +
                " AND TIMEKEY>='" + start + "'" +
                " AND TIMEKEY<'" + end + "'" +
                " AND SUBFACTORYNAME='OC'" +
                " AND EVENTNAME='TrackOut'  " +
                " and OLDPROCESSOPERATIONNAME <'L4100'" +
                " AND LINKKEY IS NOT NULL" +
                " AND INSTR(OLDPROCESSOPERATIONNAME,'DUM')=0" +
                " AND CASE WHEN LENGTH(OLDPROCESSOPERATIONNAME)>=6 THEN SUBSTR(OLDPROCESSOPERATIONNAME,6,6) ELSE 'X' END <>'D'" +
                " AND OLDPROCESSOPERATIONNAME NOT IN ('A1100','C1100')" +
                " and LENGTH(PRODUCTNAME)=10";

        String cell2MesSql = "SELECT /*+INDEX(SEG MES_PRODUCTHISTORY_IDX06)*/ 'CELL2' as shop," +
                " OLDPROCESSOPERATIONNAME as ope," +
                " PRODUCTNAME as glassId," +
                " EVENTTIME as evtTimstamp," +
                " TIMEKEY as timekey" +
                " FROM MES_PRODUCTHISTORY SEG WHERE  1=1" +
                " AND TIMEKEY>='" + start + "'" +
                " AND TIMEKEY<'" + end + "'" +
                " AND SUBFACTORYNAME='OC'" +
                " AND EVENTNAME='TrackOut'  " +
                " and OLDPROCESSOPERATIONNAME >='L4100'" +
                " AND LINKKEY IS NOT NULL" +
                " AND INSTR(OLDPROCESSOPERATIONNAME,'DUM')=0" +
                " AND CASE WHEN LENGTH(OLDPROCESSOPERATIONNAME)>=6 THEN SUBSTR(OLDPROCESSOPERATIONNAME,6,6) ELSE 'X' END <>'D'" +
                " AND OLDPROCESSOPERATIONNAME NOT IN ('A1100','C1100')" +
                " and LENGTH(PRODUCTNAME)=12";

        List<EdaDiffDModel> arrMes = test.query(arrMesSql);
        List<EdaDiffDModel> cfMes = test.query(cfMesSql);
        List<EdaDiffDModel> cell1Mes = test.query(cell1MesSql);
        List<EdaDiffDModel> cell2Mes = test.query(cell2MesSql);
//        List<EdaDiffDModel> arrMes = qr_mes.query(arrMesSql, new BeanListHandler<EdaDiffDModel>(EdaDiffDModel.class));
//        List<EdaDiffDModel> cfMes = qr_mes.query(cfMesSql, new BeanListHandler<EdaDiffDModel>(EdaDiffDModel.class));
//        List<EdaDiffDModel> cell1Mes = qr_mes.query(cell1MesSql, new BeanListHandler<EdaDiffDModel>(EdaDiffDModel.class));
//        List<EdaDiffDModel> cell2Mes = qr_mes.query(cell2MesSql, new BeanListHandler<EdaDiffDModel>(EdaDiffDModel.class));

        EdaDiffDModel arrMesCnt = getMesCnt("ARRAY", arrMes, qr_mes);
        EdaDiffDModel cfMesCnt = getMesCnt("CF", cfMes, qr_mes);
        EdaDiffDModel cell1MesCnt = getMesCnt("OC", cell1Mes, qr_mes);
        EdaDiffDModel cell2MesCnt = getMesCnt("OC", cell2Mes, qr_mes);

        //ora cnt
        String arrOraCntSqlALL = "SELECT " +
                "'ARRAY' AS SHOP," +
                " NULL AS OPE," +
                " COUNT(0) AS oraCnt " +
                " FROM ARRAY_GLASS_HST_T " +
                " WHERE glass_start_time >=to_date('" + start + "','yyyymmddhh24miss')" +
                " AND glass_start_time <to_date('" + end + "','yyyymmddhh24miss')" +
                " and LENGTH(GLASS_ID)=10" +
                " AND CASE WHEN LENGTH(STEP_ID)>=6 THEN SUBSTR(STEP_ID,6,6) ELSE 'X' END <>'D'";

        String cfOraCntSqlALL = "SELECT " +
                "'CF' AS SHOP," +
                " NULL AS OPE," +
                " COUNT(0) AS oraCnt " +
                " FROM CF_GLASS_HST_T " +
                " WHERE glass_start_time >=to_date('" + start + "','yyyymmddhh24miss')" +
                " AND glass_start_time <to_date('" + end + "','yyyymmddhh24miss')" +
                " and LENGTH(GLASS_ID)=10" +
                " AND CASE WHEN LENGTH(STEP_ID)>=6 THEN SUBSTR(STEP_ID,6,6) ELSE 'X' END <>'D'";

        String cell1OraCntSqlALL = "SELECT " +
                "'CELL1' AS SHOP," +
                " NULL AS OPE," +
                " COUNT(STEP_ID) AS oraCnt " +
                " FROM CELL_COMPONENT_HST_T " +
                " WHERE component_start_time >=to_date('" + start + "','yyyymmddhh24miss')" +
                " AND component_start_time <to_date('" + end + "','yyyymmddhh24miss')" +
                " and LENGTH(COMPONENT_ID)=10" +
                " AND CASE WHEN LENGTH(STEP_ID)>=6 THEN SUBSTR(STEP_ID,6,6) ELSE 'X' END <>'D'";

        String cell2OraCntSqlALL = "SELECT " +
                "'CELL2' AS SHOP," +
                " NULL AS OPE," +
                " COUNT(STEP_ID) AS oraCnt " +
                " FROM CELL2_CHIP_HST_T " +
                " WHERE chip_start_time >=to_date('" + start + "','yyyymmddhh24miss')" +
                " AND chip_start_time <to_date('" + end + "','yyyymmddhh24miss')" +
                " and LENGTH(CHIP_ID)=12" +
                " AND CASE WHEN LENGTH(STEP_ID)>=6 THEN SUBSTR(STEP_ID,6,6) ELSE 'X' END <>'D'" +
                " and STEP_ID<>'ADS'";//ADS数据来源不是GP

        List<EdaDiffDModel> arrOraALL = qr_ora.query(arrOraCntSqlALL, new BeanListHandler<EdaDiffDModel>(EdaDiffDModel.class));
        List<EdaDiffDModel> cfOraALL = qr_ora.query(cfOraCntSqlALL, new BeanListHandler<EdaDiffDModel>(EdaDiffDModel.class));
        List<EdaDiffDModel> cell1OraALL = qr_ora.query(cell1OraCntSqlALL, new BeanListHandler<EdaDiffDModel>(EdaDiffDModel.class));
        List<EdaDiffDModel> cell2OraALL = qr_ora.query(cell2OraCntSqlALL, new BeanListHandler<EdaDiffDModel>(EdaDiffDModel.class));


        getFinObj(arrMesCnt, startTime, endTime, updateTime, arrOraALL);
        getFinObj(cfMesCnt, startTime, endTime, updateTime, cfOraALL);
        getFinObj(cell1MesCnt, startTime, endTime, updateTime, cell1OraALL);
        getFinObj(cell2MesCnt, startTime, endTime, updateTime, cell2OraALL);

        resMsg[0] = String.valueOf(okCnt);
        resMsg[1] = String.valueOf(errCnt);
        resMsg[2] = errMsg;

        return resMsg;
    }


    private EdaDiffDModel getMesCnt(String shop, List<EdaDiffDModel> mesList, QueryRunner test) {
        EdaDiffDModel mesCnt = new EdaDiffDModel();
        HashMap<String, Timestamp> maxTimeList = new HashMap<>();
        mesCnt.setShop(shop);
        mesCnt.setOpe(null);
        if (!mesList.isEmpty()) {
            int cnt = 0;
            for(int i=0;i<mesList.size();i+=500) {
                StringBuffer sql1 = new StringBuffer();
                for(int j=0;j<500;j++) {
                    if(i+j<mesList.size()) {
                            sql1.append("('" + mesList.get(i+j).getGlassId() + "'," +
                                    "'"+ mesList.get(i+j).getOpe()+"'),");
                    }
                }
                sql1.deleteCharAt(sql1.length() - 1);
                String sql = "select PRODUCTNAME as glassId,OLDPROCESSOPERATIONNAME as ope,max(EVENTTIME) as evtTimestamp from MES_PRODUCTHISTORY SEG" +
                        " where 1=1 and EVENTNAME='TrackOut'" +
                        " and (PRODUCTNAME,OLDPROCESSOPERATIONNAME) in (" + sql1.toString() + ")" +
                        " AND LINKKEY IS NOT NULL" +
                        " GROUP BY PRODUCTNAME,OLDPROCESSOPERATIONNAME";
                try {
                    List<EdaDiffDModel> edaDiffDModelList = test.query(sql, new BeanListHandler<EdaDiffDModel>(EdaDiffDModel.class));
                edaDiffDModelList.forEach(e -> {
                    maxTimeList.put(e.getGlassId() + "_" + e.getOpe(), e.getEvtTimestamp());
                });
                }catch (Exception e){
                    logger.error(LoggerUtil.stackTraceToString(e));
                }
            }
            for(EdaDiffDModel e:mesList){
                if (e.getEvtTimestamp().equals(maxTimeList.get(e.getGlassId()+"_"+e.getOpe()))) cnt++;
                logger.info("shop={} cnt={}",e.getShop(),cnt);
            }

            mesCnt.setGpCnt(cnt);
        } else {
            mesCnt.setGpCnt(0);
        }
        return mesCnt;
    }

    private void getFinObj(EdaDiffDModel arrMesCnt, Timestamp startTime, Timestamp endTime, Timestamp updateTime, List<EdaDiffDModel> arrOraALL) {
        EdaDiffD saveObj = new EdaDiffD();
        saveObj.setShop(arrMesCnt.getShop());
        saveObj.setOpe(null);
        saveObj.setGpCnt(arrMesCnt.getGpCnt());
        if (!arrOraALL.isEmpty()) {
            saveObj.setOraCnt(arrOraALL.get(0).getOraCnt());
        } else {
            saveObj.setOraCnt(0);
        }
        saveObj.setDifference(saveObj.getGpCnt() - saveObj.getOraCnt());
        saveObj.setEvtStartTimestamp(startTime);
        saveObj.setEvtEndTimestamp(endTime);
        saveObj.setUpdateTime(updateTime);
        saveObj.setRemark("HIS");

        saveEdaDiff.saveCnt(saveObj);
    }


}
